<!-- build time:Tue Dec 17 2019 19:26:50 GMT+0800 (GMT+08:00) --><!doctype html><html class="theme-next mist" lang="zh-Hans"><head><meta name="generator" content="Hexo 3.8.0"><meta name="google-site-verification" content="7Tau9WyVgxnsEY9oYedu9g0U6_8akOX3wiKbaYcrg9A"><meta name="baidu-site-verification" content="EVwLiaxdxX"><link href="/css/xps13.css" rel="stylesheet" type="text/css"><link href="/css/message.css" rel="stylesheet" type="text/css"><script type="text/javascript" src="/js/jquery-1.11.3.min.js"></script><meta charset="UTF-8"><meta name="viewport" content="width=device-width,initial-scale=1,maximum-scale=1"><meta http-equiv="Cache-Control" content="no-transform"><meta http-equiv="Cache-Control" content="no-siteapp"><link href="/lib/font-awesome/css/font-awesome.min.css?v=4.6.2" rel="stylesheet" type="text/css"><link href="/css/main.css?v=5.1.1" rel="stylesheet" type="text/css"><meta name="keywords" content="DataBase,Oracle,Oracle 11g,"><link rel="alternate" href="/atom.xml" title="MrBird" type="application/atom+xml"><link rel="shortcut icon" type="image/x-icon" href="/favicon.ico?v=5.1.1"><meta name="description" content="文中例子涉及到的表数据：1234567891011121314151617181920SQL&amp;gt; select empno,ename,job, hiredate,sal,deptno,comm from lzp.emp;      EMPNO ENAME      JOB       HIREDATE         SAL     DEPTNO       COMM---------- -"><meta name="keywords" content="DataBase,Oracle,Oracle 11g"><meta property="og:type" content="article"><meta property="og:title" content="Oracle SQL常用函数"><meta property="og:url" content="http://mrbird.cc/Oracle-SQL常用函数.html"><meta property="og:site_name" content="MrBird"><meta property="og:description" content="文中例子涉及到的表数据：1234567891011121314151617181920SQL&amp;gt; select empno,ename,job, hiredate,sal,deptno,comm from lzp.emp;      EMPNO ENAME      JOB       HIREDATE         SAL     DEPTNO       COMM---------- -"><meta property="og:locale" content="zh-Hans"><meta property="og:updated_time" content="2019-10-28T12:14:46.259Z"><meta name="twitter:card" content="summary"><meta name="twitter:title" content="Oracle SQL常用函数"><meta name="twitter:description" content="文中例子涉及到的表数据：1234567891011121314151617181920SQL&amp;gt; select empno,ename,job, hiredate,sal,deptno,comm from lzp.emp;      EMPNO ENAME      JOB       HIREDATE         SAL     DEPTNO       COMM---------- -"><script type="text/javascript" id="hexo.configurations">var NexT=window.NexT||{},CONFIG={root:"/",scheme:"Mist",sidebar:{position:"left",display:"always",offset:12,offset_float:0,b2t:!1,scrollpercent:!1},fancybox:!1,motion:!1}</script><title>Oracle SQL常用函数 | MrBird</title></head><body ondragstart="return!1" class="animsition" lang="zh-Hans" style="overflow-x:hidden;padding-right:280px"><script type="text/javascript" src="/js/mo.min.js"></script><style>@media (min-width:768px) and (max-width:991px){body .header-innerr{width:700px!important}}.header-innerr{margin:0 auto;padding:100px 0 70px;width:880px}@media (min-width:1600px){.container .header-innerr{width:1200px}}.header-innerr{position:relative}.header-innerr{padding:20px 0 0}.header-innerr:after,.header-innerr:before{content:" ";display:table}.header-innerr:after{clear:both}@media (max-width:767px){.header-innerr{width:auto;padding:10px;margin-bottom:-20px}}</style><div class="container sidebar-position-left page-post-detail"><div class="headband"></div><header id="header" class="header"><div class="header-inner"><div class="site-brand-wrapper"><div class="site-meta"><link href="https://fonts.font.im/css?family=Merienda" rel="stylesheet"><div class="custom-logo-site-title"><a href="/" class="brand" rel="start"><span class="logo-line-before"><i></i></span> <span class="site-title" style="font-family:Merienda;font-size:1.3rem">MrBird</span> <span class="logo-line-after"><i></i></span></a></div><p class="site-subtitle"></p></div><div class="site-nav-toggle"><button><span class="btn-bar"></span> <span class="btn-bar"></span> <span class="btn-bar"></span></button></div></div><nav class="site-nav"><ul id="menu" class="menu"><li class="menu-item menu-item-home"><a href="/" rel="section">HOME</a></li><li class="menu-item menu-item-archives"><a href="/archives/" rel="section">ARCHIVES</a></li><li class="menu-item menu-item-tags"><a href="/tags/" rel="section">TAGS</a></li><li class="menu-item menu-item-friends"><a href="/friends/" rel="section">FRIENDS</a></li><div class="sidebar-toggle" style="display:none"><div class="sidebar-toggle-line-wrap"><span class="sidebar-toggle-line sidebar-toggle-line-first"></span> <span class="sidebar-toggle-line sidebar-toggle-line-middle"></span> <span class="sidebar-toggle-line sidebar-toggle-line-last"></span></div></div></ul><div class="site-search"><div class="popup search-popup local-search-popup"><div class="local-search-header clearfix"><span class="search-icon"><i class="fa fa-search"></i> </span><span class="popup-btn-close"><i class="fa fa-times-circle"></i></span><div class="local-search-input-wrapper"><input autocomplete="off" placeholder="Search" spellcheck="false" type="text" id="local-search-input"></div></div><div id="local-search-result"></div></div></div></nav></div><div class="header-innerr"><div class="note info" style="margin:0;letter-spacing:.15px">🐤手把手教你搭建<strong>Spring Cloud微服务权限系统</strong>（从零到部署）：<a style="color:#40dab2;font-weight:600" href="https://www.kancloud.cn/mrbird/spring-cloud" target="_blank">https://www.kancloud.cn/mrbird/spring-cloud</a></div></div></header><main id="main" class="main"><div class="main-inner"><div class="content-wrap"><div id="content" class="content"><div id="posts" class="posts-expand"><article class="post post-type-normal" itemscope itemtype="http://schema.org/Article"><link itemprop="mainEntityOfPage" href="http://mrbird.cc/Oracle-SQL常用函数.html"><span hidden itemprop="author" itemscope itemtype="http://schema.org/Person"><meta itemprop="name" content="MrBird"><meta itemprop="description" content=""><meta itemprop="image" content="/images/blogImage.jpg"></span><span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization"><meta itemprop="name" content="MrBird"></span><header class="post-header"><h1 class="post-title" itemprop="name headline">Oracle SQL常用函数</h1><div class="post-meta"><span class="post-time"><span class="post-meta-item-icon"><i class="fa fa-calendar-o"></i> </span><span class="post-meta-item-text">Posted on</span> <time title="创建于" itemprop="dateCreated datePublished" datetime="2016-09-06T10:35:24+08:00">2016-09-06 </time></span><span></span> <span class="post-meta-divider">|</span> <span class="page-pv"><i class="fa fa-laptop"></i>&nbsp;&nbsp;Visit count <span class="busuanzi-value" id="busuanzi_value_page_pv"></span></span></div></header><div class="post-body" itemprop="articleBody"><p>文中例子涉及到的表数据：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select empno,ename,job, hiredate,sal,deptno,comm from lzp.emp;</span><br><span class="line"> </span><br><span class="line">     EMPNO ENAME      JOB       HIREDATE         SAL     DEPTNO       COMM</span><br><span class="line"><span class="comment">---------- ---------- --------- -------------- ------- ---------- ----------</span></span><br><span class="line">      7369 SMITH      CLERK     17-12月-80       800         20        null</span><br><span class="line">      7499 ALLEN      SALESMAN  20-2月 -81      3100         30        300</span><br><span class="line">      7521 WARD       SALESMAN  22-2月 -81      1250         30        500</span><br><span class="line">      7566 JONES      MANAGER   02-4月 -81      2975         20        null</span><br><span class="line">      7654 MARTIN     SALESMAN  28-9月 -81      8000         30        1400</span><br><span class="line">      7698 BLAKE      MANAGER   01-5月 -81      2850         30        null</span><br><span class="line">      7782 CLARK      MANAGER   09-6月 -81      2450         10        null</span><br><span class="line">      7788 SCOTT      ANALYST   19-4月 -87      3000         20        null</span><br><span class="line">      7839 KING       PRESIDENT 17-11月-81      5000         10        null</span><br><span class="line">      7844 TURNER     SALESMAN  08-9月 -81      1500         30         0</span><br><span class="line">      7876 ADAMS      CLERK     23-5月 -87      1100         20        null</span><br><span class="line">      7900 JAMES      CLERK     03-12月-81       950         30        null</span><br><span class="line">      7902 FORD       ANALYST   03-12月-81      3000         20        null</span><br><span class="line">      7934 MILLER     CLERK     23-1月 -82      1300         10        null</span><br><span class="line"> </span><br><span class="line">已选择14行。</span><br></pre></td></tr></table></figure><p></p><a id="more"></a><h2 id="字符函数"><a href="#字符函数" class="headerlink" title="字符函数"></a>字符函数</h2><h3 id="字符串截取"><a href="#字符串截取" class="headerlink" title="字符串截取"></a>字符串截取</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select substr('mrbird',3,6) from dual;</span><br><span class="line"> </span><br><span class="line">SUBS</span><br><span class="line"><span class="comment">----</span></span><br><span class="line">bird</span><br></pre></td></tr></table></figure><h3 id="查找子串位置"><a href="#查找子串位置" class="headerlink" title="查找子串位置"></a>查找子串位置</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select instr('mrBird','Bird') from dual;</span><br><span class="line"> </span><br><span class="line">INSTR('MRBIRD','BIRD')</span><br><span class="line"><span class="comment">----------------------</span></span><br><span class="line">                     3</span><br></pre></td></tr></table></figure><h3 id="字符串连接"><a href="#字符串连接" class="headerlink" title="字符串连接"></a>字符串连接</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/* 1. || */</span></span><br><span class="line">SQL&gt; select 'mr'||'Bird'as result from dual;</span><br><span class="line"> </span><br><span class="line">RESULT</span><br><span class="line"><span class="comment">------</span></span><br><span class="line">mrBird</span><br><span class="line"><span class="comment">/* 2.concat */</span></span><br><span class="line">SQL&gt; select concat('mr','Bird') as result from dual;</span><br><span class="line"> </span><br><span class="line">RESULT</span><br><span class="line"><span class="comment">------</span></span><br><span class="line">mrBird</span><br></pre></td></tr></table></figure><h3 id="去除空格"><a href="#去除空格" class="headerlink" title="去除空格"></a>去除空格</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">SQL &gt; </span><br><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">    <span class="keyword">LTRIM</span> (<span class="string">'   mrBird'</span>) l,</span><br><span class="line">    <span class="keyword">RTRIM</span> (<span class="string">'mrBird   '</span>) r,</span><br><span class="line">    <span class="keyword">TRIM</span> (<span class="string">'  mrBird  '</span>) T</span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">    dual;</span><br><span class="line"> </span><br><span class="line">L      R      T</span><br><span class="line"><span class="comment">------ ------ ------</span></span><br><span class="line">mrBird mrBird mrBird</span><br></pre></td></tr></table></figure><h3 id="去除前缀和后缀"><a href="#去除前缀和后缀" class="headerlink" title="去除前缀和后缀"></a>去除前缀和后缀</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">SQL &gt; </span><br><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">    <span class="keyword">TRIM</span> (<span class="keyword">LEADING</span> <span class="string">'M'</span> <span class="keyword">FROM</span> <span class="string">'MMMMMmrBird'</span>) s1,</span><br><span class="line">    <span class="keyword">TRIM</span> (TRAILING <span class="string">'D'</span> <span class="keyword">FROM</span> <span class="string">'mrBirdDDDD'</span>) s2,</span><br><span class="line">    <span class="keyword">TRIM</span> (<span class="string">'M'</span> <span class="keyword">FROM</span> <span class="string">'MMMmrBirdMMMM'</span>) s3</span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">    dual; </span><br><span class="line">    </span><br><span class="line">S1     S2     S3</span><br><span class="line"><span class="comment">------ ------ ------</span></span><br><span class="line">mrBird mrBird mrBird</span><br></pre></td></tr></table></figure><h3 id="计算字符串长度"><a href="#计算字符串长度" class="headerlink" title="计算字符串长度"></a>计算字符串长度</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select length('mrbird') from dual;</span><br><span class="line"> </span><br><span class="line">LENGTH('MRBIRD')</span><br><span class="line"><span class="comment">----------------</span></span><br><span class="line">               6</span><br></pre></td></tr></table></figure><h3 id="initcap（首字母变大写）-lower（变小写）-upper（变大写）"><a href="#initcap（首字母变大写）-lower（变小写）-upper（变大写）" class="headerlink" title="initcap（首字母变大写） ,lower（变小写）,upper（变大写）"></a>initcap（首字母变大写） ,lower（变小写）,upper（变大写）</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select initcap('mrbird') s1,lower('MRBIRD') s2,upper('mrbird') s3 from dual;</span><br><span class="line"> </span><br><span class="line">S1     S2     S3</span><br><span class="line"><span class="comment">------ ------ ------</span></span><br><span class="line">Mrbird mrbird MRBIRD</span><br></pre></td></tr></table></figure><h3 id="替换"><a href="#替换" class="headerlink" title="替换"></a>替换</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select replace('mrXiaoniao','Xiaoniao','Bird') from dual;</span><br><span class="line"> </span><br><span class="line">REPLAC</span><br><span class="line"><span class="comment">------</span></span><br><span class="line">mrBird</span><br></pre></td></tr></table></figure><h3 id="decode-实现if-then-逻辑-注-第一个是表达式-最后一个是不满足任何一个条件的值"><a href="#decode-实现if-then-逻辑-注-第一个是表达式-最后一个是不满足任何一个条件的值" class="headerlink" title="decode[实现if ..then 逻辑] 注:第一个是表达式,最后一个是不满足任何一个条件的值"></a>decode[实现if ..then 逻辑] 注:第一个是表达式,最后一个是不满足任何一个条件的值</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; </span><br><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">    <span class="keyword">DECODE</span> (</span><br><span class="line">        deptno,</span><br><span class="line">        <span class="number">10</span>,</span><br><span class="line">        <span class="string">'人力资源部'</span>,</span><br><span class="line">        <span class="number">20</span>,</span><br><span class="line">        <span class="string">'软件开发部'</span>,</span><br><span class="line">        <span class="number">30</span>,</span><br><span class="line">        <span class="string">'市场部'</span>,</span><br><span class="line">        <span class="string">'其他神秘部门'</span></span><br><span class="line">    ) deptName,</span><br><span class="line">    deptno,</span><br><span class="line">    ename</span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">    lzp.emp</span><br><span class="line"><span class="keyword">WHERE</span></span><br><span class="line">    empno &gt;= <span class="number">7900</span>;</span><br><span class="line"> </span><br><span class="line">DEPTNAME         DEPTNO ENAME</span><br><span class="line"><span class="comment">------------ ---------- ----------</span></span><br><span class="line">市场部               30 JAMES</span><br><span class="line">软件开发部           20 FORD</span><br><span class="line">人力资源部           10 MILLER</span><br></pre></td></tr></table></figure><h3 id="case-实现switch-case-逻辑"><a href="#case-实现switch-case-逻辑" class="headerlink" title="case[实现switch ..case 逻辑]"></a>case[实现switch ..case 逻辑]</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; </span><br><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">    <span class="keyword">CASE</span></span><br><span class="line"><span class="keyword">WHEN</span> sal &lt; <span class="number">2000</span> <span class="keyword">THEN</span></span><br><span class="line">    <span class="string">'被剥削的人'</span></span><br><span class="line"><span class="keyword">WHEN</span> sal &lt; <span class="number">3000</span> <span class="keyword">THEN</span></span><br><span class="line">    <span class="string">'被压榨的人'</span></span><br><span class="line"><span class="keyword">WHEN</span> sal &lt; <span class="number">5000</span> <span class="keyword">THEN</span></span><br><span class="line">    <span class="string">'普通的人'</span></span><br><span class="line"><span class="keyword">ELSE</span></span><br><span class="line">    <span class="string">'努力的人'</span></span><br><span class="line"><span class="keyword">END</span> meno,</span><br><span class="line"> sal,</span><br><span class="line"> ename</span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">    lzp.emp</span><br><span class="line"><span class="keyword">WHERE</span></span><br><span class="line">    deptno = <span class="number">30</span></span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span></span><br><span class="line">    sal <span class="keyword">ASC</span>;</span><br><span class="line">    </span><br><span class="line">MENO              SAL ENAME</span><br><span class="line"><span class="comment">---------- ---------- ----------</span></span><br><span class="line">被剥削的人        950 JAMES</span><br><span class="line">被剥削的人       1250 WARD</span><br><span class="line">被剥削的人       1500 TURNER</span><br><span class="line">被压榨的人       2850 BLAKE</span><br><span class="line">普通的人         3100 ALLEN</span><br><span class="line">努力的人         8000 MARTIN</span><br><span class="line"> </span><br><span class="line">已选择6行。</span><br></pre></td></tr></table></figure><h3 id="十进制和ASCII互相转换"><a href="#十进制和ASCII互相转换" class="headerlink" title="十进制和ASCII互相转换"></a>十进制和ASCII互相转换</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select ascii('A'),chr(65) from dual;</span><br><span class="line"> </span><br><span class="line">ASCII('A') C</span><br><span class="line"><span class="comment">---------- -</span></span><br><span class="line">        65 A</span><br></pre></td></tr></table></figure><h2 id="日期函数"><a href="#日期函数" class="headerlink" title="日期函数"></a>日期函数</h2><p>TO_DATE格式(以时间: 2007-11-02 13:45:25 为例)</p><table><tr><td colspan="4"><strong>Year</strong></td></tr><tr><td>yy &nbsp; &nbsp;<br></td><td>two digits</td><td>两位年</td><td>显示值:07</td></tr><tr><td>yyy</td><td>three digits</td><td>三位年</td><td>显示值:007</td></tr><tr><td>yyyy</td><td>four digits</td><td>四位年</td><td>显示值:2007</td></tr><tr><td colspan="4"><strong>Month</strong></td></tr><tr><td>mm &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;</td><td>number&nbsp;</td><td>两位月&nbsp;</td><td>显示值:11</td></tr><tr><td>mon&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;</td><td>abbreviated</td><td>字符集表示</td><td>显示值:11月,若是英文版,显示nov&nbsp;</td></tr><tr><td>month</td><td>spelled out</td><td>字符集表示</td><td>显示值:11月,若是英文版,显示november</td></tr><tr><td colspan="4"><strong>Day</strong></td></tr><tr><td>dd &nbsp; &nbsp; &nbsp;&nbsp;</td><td>number</td><td>当月第几天</td><td>显示值:02</td></tr><tr><td>ddd&nbsp;&nbsp;</td><td>number</td><td>当年第几天</td><td>显示值:306</td></tr><tr><td>dy &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp;</td><td>abbreviated &nbsp; &nbsp;</td><td>当周第几天简写&nbsp;</td><td>显示值:星期五,若是英文版,显示fri</td></tr><tr><td>day</td><td>spelled out</td><td>当周第几天全写</td><td>显示值:星期五,若是英文版,显示friday</td></tr><tr><td>ddspth</td><td>spelled out</td><td>当月第几天英文</td><td>显示值:second</td></tr><tr><td colspan="4"><strong>Hour</strong></td></tr><tr><td>hh &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;</td><td>two digits</td><td>12小时进制&nbsp;</td><td>显示值:01</td></tr><tr><td>hh24</td><td>two digits</td><td>24小时进制</td><td>显示值:13</td></tr><tr><td colspan="4"><strong>Minute </strong>&nbsp;</td></tr><tr><td>&nbsp;mi/mm &nbsp; &nbsp;&nbsp;</td><td>two digits</td><td>60进制</td><td>显示值:45</td></tr><tr><td colspan="4"><strong>Second</strong></td></tr><tr><td>ss &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;</td><td>two digits</td><td>60进制</td><td>显示值:25</td></tr><tr><td colspan="4"><strong>Others</strong></td></tr><tr><td>Q &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp;</td><td>digit</td><td>季度</td><td>显示值:4</td></tr><tr><td>WW</td><td>digit</td><td>当年第几周&nbsp;</td><td>显示值:44</td></tr><tr><td>W</td><td>digit</td><td>当月第几周</td><td>显示值:1</td></tr></table><p>24小时格式下时间范围为： 0:00:00 - 23:59:59….</p><p>12小时格式下时间范围为： 1:00:00 - 12:59:59 ….</p><h3 id="日期字符串相互转换"><a href="#日期字符串相互转换" class="headerlink" title="日期字符串相互转换"></a>日期字符串相互转换</h3><p>时间转字符串:</p><p>Year：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt;  </span><br><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">    TO_CHAR (<span class="keyword">SYSDATE</span>, <span class="string">'yy'</span>) yy,</span><br><span class="line">    TO_CHAR (<span class="keyword">SYSDATE</span>, <span class="string">'yyy'</span>) yyy,</span><br><span class="line">    TO_CHAR (<span class="keyword">SYSDATE</span>, <span class="string">'yyyy'</span>) yyyy</span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">    dual;</span><br><span class="line">    </span><br><span class="line">YY YYY YYYY</span><br><span class="line"><span class="comment">-- --- ----</span></span><br><span class="line">16 016 2016</span><br></pre></td></tr></table></figure><p></p><p>Month：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt;</span><br><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">    TO_CHAR (<span class="keyword">SYSDATE</span>, <span class="string">'mm'</span>) mm,</span><br><span class="line">    TO_CHAR (<span class="keyword">SYSDATE</span>, <span class="string">'mon'</span>) mon,</span><br><span class="line">    TO_CHAR (<span class="keyword">SYSDATE</span>, <span class="string">'month'</span>) <span class="keyword">month</span></span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">    dual;</span><br><span class="line"> </span><br><span class="line">MM MON      MONTH</span><br><span class="line"><span class="comment">-- -------- ------</span></span><br><span class="line">09 9月      9月</span><br></pre></td></tr></table></figure><p></p><p>Day：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt;</span><br><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">    TO_CHAR (<span class="keyword">SYSDATE</span>, <span class="string">'dd'</span>) dd,</span><br><span class="line">    TO_CHAR (<span class="keyword">SYSDATE</span>, <span class="string">'ddd'</span>) ddd,</span><br><span class="line">    TO_CHAR (<span class="keyword">SYSDATE</span>, <span class="string">'dy'</span>) dy,</span><br><span class="line">    TO_CHAR (<span class="keyword">SYSDATE</span>, <span class="string">'day'</span>) <span class="keyword">DAY</span>,</span><br><span class="line">    TO_CHAR (<span class="keyword">SYSDATE</span>, <span class="string">'ddspth'</span>) ddspth</span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">    dual;</span><br><span class="line">    </span><br><span class="line">DD DDD DY           DAY       DDSPTH</span><br><span class="line"><span class="comment">-- --- ------------ --------- --------------</span></span><br><span class="line">06 250 tue       tuesday      sixth</span><br></pre></td></tr></table></figure><p></p><p>Hour：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt;</span><br><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">    TO_CHAR (<span class="keyword">SYSDATE</span>, <span class="string">'hh'</span>) hh,</span><br><span class="line">    TO_CHAR (<span class="keyword">SYSDATE</span>, <span class="string">'hh24'</span>) hh24</span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">    dual;</span><br><span class="line">    </span><br><span class="line">HH HH24</span><br><span class="line"><span class="comment">-- --</span></span><br><span class="line">05 17</span><br></pre></td></tr></table></figure><p></p><p>Minute,Second略</p><p>季度，周：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt;</span><br><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">    TO_CHAR (<span class="keyword">SYSDATE</span>, <span class="string">'Q'</span>) Q,</span><br><span class="line">    TO_CHAR (<span class="keyword">SYSDATE</span>, <span class="string">'WW'</span>) WW,</span><br><span class="line">    TO_CHAR (<span class="keyword">SYSDATE</span>, <span class="string">'W'</span>) W</span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">    dual;</span><br><span class="line">    </span><br><span class="line">Q WW W</span><br><span class="line">- <span class="comment">-- -</span></span><br><span class="line">3 36 1</span><br></pre></td></tr></table></figure><p></p><p>字符串转时间：</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select to_date('2016-09-06','yyyy-MM-dd') time from dual;</span><br><span class="line"> </span><br><span class="line">TIME</span><br><span class="line"><span class="comment">--------------</span></span><br><span class="line">06-9月 -16</span><br></pre></td></tr></table></figure><p></p><h3 id="next-day"><a href="#next-day" class="headerlink" title="next_day"></a>next_day</h3><p>返回下个星期的日期,day为1-7或星期日-星期六,1表示星期日，也可以用英文表示星期。</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select next_day(sysdate,'monday') time from dual;</span><br><span class="line"> </span><br><span class="line">TIME</span><br><span class="line"><span class="comment">--------------</span></span><br><span class="line">12-9月 -16</span><br></pre></td></tr></table></figure><p></p><h3 id="两个日期相差天数"><a href="#两个日期相差天数" class="headerlink" title="两个日期相差天数"></a>两个日期相差天数</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select floor(sysdate - to_date('20020405','yyyymmdd')) time from dual;</span><br><span class="line"> </span><br><span class="line">      TIME</span><br><span class="line"><span class="comment">----------</span></span><br><span class="line">      5268</span><br></pre></td></tr></table></figure><h3 id="months-between"><a href="#months-between" class="headerlink" title="months_between"></a>months_between</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; </span><br><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">    MONTHS_BETWEEN (</span><br><span class="line">        <span class="keyword">TO_DATE</span> (<span class="string">'2016-09-01'</span>, <span class="string">'yyyy-MM-dd'</span>),</span><br><span class="line">        <span class="keyword">TO_DATE</span> (<span class="string">'2016-01-01'</span>, <span class="string">'yyyy-MM-dd'</span>)</span><br><span class="line">    ) <span class="keyword">months</span></span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">    dual;</span><br><span class="line">    </span><br><span class="line">    MONTHS</span><br><span class="line"><span class="comment">----------</span></span><br><span class="line">         8</span><br></pre></td></tr></table></figure><h3 id="round-舍入到最接近的日期-（day-舍入到最接近的星期日）"><a href="#round-舍入到最接近的日期-（day-舍入到最接近的星期日）" class="headerlink" title="round [舍入到最接近的日期]（day:舍入到最接近的星期日）"></a>round [舍入到最接近的日期]（day:舍入到最接近的星期日）</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; </span><br><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">    <span class="keyword">SYSDATE</span> S1,</span><br><span class="line">    <span class="keyword">ROUND</span> (<span class="keyword">SYSDATE</span>) S2,</span><br><span class="line">    <span class="keyword">ROUND</span> (<span class="keyword">SYSDATE</span>, <span class="string">'year'</span>) <span class="keyword">YEAR</span>,</span><br><span class="line">    <span class="keyword">ROUND</span> (<span class="keyword">SYSDATE</span>, <span class="string">'month'</span>) <span class="keyword">MONTH</span>,</span><br><span class="line">    <span class="keyword">ROUND</span> (<span class="keyword">SYSDATE</span>, <span class="string">'day'</span>) <span class="keyword">DAY</span></span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">    dual;</span><br><span class="line">    </span><br><span class="line">S1             S2             YEAR           MONTH          DAY</span><br><span class="line"><span class="comment">-------------- -------------- -------------- -------------- --------------</span></span><br><span class="line">07-9月 -16     07-9月 -16     01-1月 -17     01-9月 -16     04-9月 -16</span><br></pre></td></tr></table></figure><h3 id="计算时间差"><a href="#计算时间差" class="headerlink" title="计算时间差"></a>计算时间差</h3><p>注:oracle时间差是以天数为单位,所以换算成年月,日</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt;</span><br><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">    <span class="keyword">FLOOR</span> (</span><br><span class="line">        TO_NUMBER (</span><br><span class="line">            <span class="keyword">SYSDATE</span> - <span class="keyword">TO_DATE</span> (</span><br><span class="line">                <span class="string">'2007-11-02 15:55:03'</span>,</span><br><span class="line">                <span class="string">'yyyy-mm-dd hh24:mi:ss'</span></span><br><span class="line">            )</span><br><span class="line">        ) / <span class="number">365</span></span><br><span class="line">    ) <span class="keyword">AS</span> spanYears</span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">    dual;</span><br><span class="line"> </span><br><span class="line"> SPANYEARS</span><br><span class="line"><span class="comment">----------</span></span><br><span class="line">         8</span><br><span class="line">SQL&gt;</span><br><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">    <span class="keyword">CEIL</span> (</span><br><span class="line">        MONTHS_BETWEEN (</span><br><span class="line">            <span class="keyword">SYSDATE</span>,</span><br><span class="line">            <span class="keyword">TO_DATE</span> (</span><br><span class="line">                <span class="string">'2007-11-02 15:55:03'</span>,</span><br><span class="line">                <span class="string">'yyyy-mm-dd hh24:mi:ss'</span></span><br><span class="line">            )</span><br><span class="line">        )</span><br><span class="line">    ) <span class="keyword">AS</span> spanMonths</span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">    dual;</span><br><span class="line">    </span><br><span class="line">SPANMONTHS</span><br><span class="line"><span class="comment">----------</span></span><br><span class="line">       107</span><br><span class="line">SQL&gt;</span><br><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">    <span class="keyword">FLOOR</span> (</span><br><span class="line">        TO_NUMBER (</span><br><span class="line">            <span class="keyword">SYSDATE</span> - <span class="keyword">TO_DATE</span> (</span><br><span class="line">                <span class="string">'2007-11-02 15:55:03'</span>,</span><br><span class="line">                <span class="string">'yyyy-mm-dd hh24:mi:ss'</span></span><br><span class="line">            )</span><br><span class="line">        )</span><br><span class="line">    ) <span class="keyword">AS</span> spanDays</span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">    dual;</span><br><span class="line">    </span><br><span class="line">  SPANDAYS</span><br><span class="line"><span class="comment">----------</span></span><br><span class="line">      3231</span><br><span class="line">SQL&gt;</span><br><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">    <span class="keyword">FLOOR</span> (</span><br><span class="line">        TO_NUMBER (</span><br><span class="line">            <span class="keyword">SYSDATE</span> - <span class="keyword">TO_DATE</span> (</span><br><span class="line">                <span class="string">'2007-11-02 15:55:03'</span>,</span><br><span class="line">                <span class="string">'yyyy-mm-dd hh24:mi:ss'</span></span><br><span class="line">            )</span><br><span class="line">        ) * <span class="number">24</span></span><br><span class="line">    ) <span class="keyword">AS</span> spanHours</span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">    dual;</span><br><span class="line">    </span><br><span class="line"> SPANHOURS</span><br><span class="line"><span class="comment">----------</span></span><br><span class="line">     77562</span><br><span class="line"><span class="comment">/* 分秒略，以此类推 */</span></span><br></pre></td></tr></table></figure><p></p><h3 id="查找月的最后一天"><a href="#查找月的最后一天" class="headerlink" title="查找月的最后一天"></a>查找月的最后一天</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select last_day(sysdate) from dual;</span><br><span class="line"> </span><br><span class="line">LAST_DAY(SYSDA</span><br><span class="line"><span class="comment">--------------</span></span><br><span class="line">30-9月 -16</span><br></pre></td></tr></table></figure><h3 id="ADD-MONTHS-date，i"><a href="#ADD-MONTHS-date，i" class="headerlink" title="ADD_MONTHS(date，i)"></a>ADD_MONTHS(date，i)</h3><p>对给定的日期加上给定的月，查看每个员工入职20周年</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select ename,add_months(hiredate,12*20) from lzp.emp;</span><br><span class="line"> </span><br><span class="line">ENAME      ADD_MONTHS(HIR</span><br><span class="line"><span class="comment">---------- --------------</span></span><br><span class="line">SMITH      17-12月-00</span><br><span class="line">ALLEN      20-2月 -01</span><br><span class="line">WARD       22-2月 -01</span><br><span class="line">JONES      02-4月 -01</span><br><span class="line">MARTIN     28-9月 -01</span><br><span class="line">BLAKE      01-5月 -01</span><br><span class="line">CLARK      09-6月 -01</span><br><span class="line">SCOTT      19-4月 -07</span><br><span class="line">KING       17-11月-01</span><br><span class="line">TURNER     08-9月 -01</span><br><span class="line">ADAMS      23-5月 -07</span><br><span class="line">JAMES      03-12月-01</span><br><span class="line">FORD       03-12月-01</span><br><span class="line">MILLER     23-1月 -02</span><br><span class="line"></span><br><span class="line">已选择14行。</span><br></pre></td></tr></table></figure><p></p><h3 id="EXTRACT"><a href="#EXTRACT" class="headerlink" title="EXTRACT"></a>EXTRACT</h3><p>单独获取指定时间的年或月或日</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select extract(year from sysdate) from dual;</span><br><span class="line"> </span><br><span class="line">EXTRACT(YEARFROMSYSDATE)</span><br><span class="line"><span class="comment">------------------------</span></span><br><span class="line">                    2016</span><br></pre></td></tr></table></figure><p></p><h2 id="数字函数"><a href="#数字函数" class="headerlink" title="数字函数"></a>数字函数</h2><h3 id="取整函数（ceil-向上取整-floor-向下取整）"><a href="#取整函数（ceil-向上取整-floor-向下取整）" class="headerlink" title="取整函数（ceil 向上取整,floor 向下取整）"></a>取整函数（ceil 向上取整,floor 向下取整）</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select ceil(66.6) N1,floor(66.6) N2 from dual;</span><br><span class="line"> </span><br><span class="line">        N1         N2</span><br><span class="line"><span class="comment">---------- ----------</span></span><br><span class="line">        67         66</span><br></pre></td></tr></table></figure><h3 id="取幂-power-和-求平方根-sqrt"><a href="#取幂-power-和-求平方根-sqrt" class="headerlink" title="取幂(power) 和 求平方根(sqrt)"></a>取幂(power) 和 求平方根(sqrt)</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt;  select power(3,2) N1,sqrt(9) N2 from dual;</span><br><span class="line"> </span><br><span class="line">        N1         N2</span><br><span class="line"><span class="comment">---------- ----------</span></span><br><span class="line">         9          3</span><br></pre></td></tr></table></figure><h3 id="求余"><a href="#求余" class="headerlink" title="求余"></a>求余</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt;  select mod(9,5) from dual;</span><br><span class="line"> </span><br><span class="line">  MOD(9,5)</span><br><span class="line"><span class="comment">----------</span></span><br><span class="line">         4</span><br></pre></td></tr></table></figure><h3 id="返回固定小数位数-（round-四舍五入，trunc-直接截断）"><a href="#返回固定小数位数-（round-四舍五入，trunc-直接截断）" class="headerlink" title="返回固定小数位数 （round:四舍五入，trunc:直接截断）"></a>返回固定小数位数 （round:四舍五入，trunc:直接截断）</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select round(66.667,2) N1,trunc(66.667,2) N2 from dual;</span><br><span class="line"> </span><br><span class="line">        N1         N2</span><br><span class="line"><span class="comment">---------- ----------</span></span><br><span class="line">     66.67      66.66</span><br></pre></td></tr></table></figure><h3 id="返回值的符号（正数返回为1-负数为-1）"><a href="#返回值的符号（正数返回为1-负数为-1）" class="headerlink" title="返回值的符号（正数返回为1,负数为-1）"></a>返回值的符号（正数返回为1,负数为-1）</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select sign(-32),sign(293) from dual;</span><br><span class="line"> </span><br><span class="line"> SIGN(-32)  SIGN(293)</span><br><span class="line"><span class="comment">---------- ----------</span></span><br><span class="line">        -1          1</span><br></pre></td></tr></table></figure><h3 id="LEAST、GREATEST"><a href="#LEAST、GREATEST" class="headerlink" title="LEAST、GREATEST"></a>LEAST、GREATEST</h3><p>两个函数都可以有多个参数值，但参数类型必须一致，返回结果是参数列表中最大或最小的值。 在比较之前，在参数列表中第二个以后的参数会被隐含的转换为第一个参数的数据类型，所以如果可以转换，则继续比较，如果不能转换将会报错。</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select least(1,2,3),greatest(sysdate,'01-10月-16') from dual;</span><br><span class="line"> </span><br><span class="line">LEAST(1,2,3) GREATEST(SYSDA</span><br><span class="line"><span class="comment">------------ --------------</span></span><br><span class="line">           1 01-10月-16</span><br></pre></td></tr></table></figure><p></p><h2 id="其他函数"><a href="#其他函数" class="headerlink" title="其他函数"></a>其他函数</h2><h3 id="vsize-返回表达式所需的字节数"><a href="#vsize-返回表达式所需的字节数" class="headerlink" title="vsize: 返回表达式所需的字节数"></a>vsize: 返回表达式所需的字节数</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select vsize('mrBird123') from dual;</span><br><span class="line"> </span><br><span class="line">VSIZE('MRBIRD123')</span><br><span class="line"><span class="comment">------------------</span></span><br><span class="line">                 9</span><br></pre></td></tr></table></figure><h3 id="nvl-ex1-ex2"><a href="#nvl-ex1-ex2" class="headerlink" title="nvl(ex1,ex2):"></a>nvl(ex1,ex2):</h3><p>ex1值为空则返回ex2,否则返回该值本身ex1（常用） 例：如果雇员没有佣金，将显示0，否则显示佣金</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select comm,nvl(comm,0) from lzp.emp;</span><br><span class="line"> </span><br><span class="line">      COMM NVL(COMM,0)</span><br><span class="line"><span class="comment">---------- -----------</span></span><br><span class="line">                     0</span><br><span class="line">       300         300</span><br><span class="line">       500         500</span><br><span class="line">                     0</span><br><span class="line">      1400        1400</span><br><span class="line">                     0</span><br><span class="line">                     0</span><br><span class="line">                     0</span><br><span class="line">                     0</span><br><span class="line">         0           0</span><br><span class="line">                     0</span><br><span class="line">                     0</span><br><span class="line">                     0</span><br><span class="line">                     0</span><br><span class="line"> </span><br><span class="line">已选择14行。</span><br></pre></td></tr></table></figure><p></p><h3 id="nullif-ex1-ex2"><a href="#nullif-ex1-ex2" class="headerlink" title="nullif(ex1,ex2):"></a>nullif(ex1,ex2):</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select nullif(88,88) from dual;</span><br><span class="line"> </span><br><span class="line">NULLIF(88,88)</span><br><span class="line"><span class="comment">-------------</span></span><br><span class="line"> </span><br><span class="line">SQL&gt; select nullif(88,123) from dual;</span><br><span class="line"> </span><br><span class="line">NULLIF(88,123)</span><br><span class="line"><span class="comment">--------------</span></span><br><span class="line">            88</span><br><span class="line">SQL&gt; select nullif('mrBird','mrBird') from dual;</span><br><span class="line"> </span><br><span class="line">NULLIF</span><br><span class="line"><span class="comment">------</span></span><br></pre></td></tr></table></figure><h3 id="nvl2-ex1-ex2-ex3"><a href="#nvl2-ex1-ex2-ex3" class="headerlink" title="nvl2(ex1,ex2,ex3) :"></a>nvl2(ex1,ex2,ex3) :</h3><p>如果ex1不为空，显示ex2,否则显示ex3</p><blockquote><p>注：部分内容来自<a href="http://www.jb51.net/" target="_blank" rel="noopener">脚本之家</a></p></blockquote><script>$(".post-body a").not(".thispage").addClass("ignore-href").attr("target","_blank")</script></div><div></div><div><div style="padding:10px 0;margin:20px auto;width:90%;text-align:center;color:#878787"><div>请作者喝瓶肥宅水~</div><button id="rewardButton" style="margin-top:10px" disable="enable" onclick='var e=document.getElementById("QR");"none"===e.style.display?e.style.display="block":e.style.display="none"'><span style="height:46px;width:46px;line-height:46px;border-radius:50%;color:#fe5f55;font-weight:600;background:#ffd5be;border:none;box-shadow:0 4px 8px 0 rgba(255,213,190,.4)">￥</span></button><div id="QR" style="display:none"><div id="wechat" style="display:inline-block"><img id="wechat_qr" src="/img/wechat_pay.png" alt="MrBird WeChat Pay"></div><div id="alipay" style="display:inline-block"><img id="alipay_qr" src="/img/ali_pay.png" alt="MrBird Alipay"></div></div></div><style>#QR img{width:auto;margin:.8em 1em 0 1em}</style></div><div><ul class="post-copyright"><li class="post-copyright-author"><strong>本文作者：</strong> MrBird</li><li class="post-copyright-link"><strong>本文链接：</strong> <a href="http://mrbird.cc/Oracle-SQL常用函数.html" title="Oracle SQL常用函数">http://mrbird.cc/Oracle-SQL常用函数.html</a></li><li class="post-copyright-license"><strong>版权声明： </strong>本博客所有文章除特别声明外，均采用 <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/" rel="external nofollow" target="_blank">CC BY-NC-SA 4.0</a> 许可协议。转载请注明出处！</li></ul></div><footer class="post-footer"><div class="post-tags" style="margin-bottom:1.3rem"><a href="/tags/DataBase/" rel="tag"># DataBase</a> <a href="/tags/Oracle/" rel="tag"># Oracle</a> <a href="/tags/Oracle-11g/" rel="tag"># Oracle 11g</a></div><div class="post-nav"><div class="post-nav-next post-nav-item"><a href="/Java-Encryption-Decryption.html" rel="next" title="Java Encryption - Decryption"><i class="fa fa-chevron-left"></i> Java Encryption - Decryption</a></div><span class="post-nav-divider"></span><div class="post-nav-prev post-nav-item"><a href="/Java-反射机制.html" rel="prev" title="Java 反射机制">Java 反射机制 <i class="fa fa-chevron-right"></i></a></div></div></footer></article><hr><div id="container"></div><div class="post-spread"><div id="comment-div"></div><style>.valine .vlist{margin-bottom:3rem}.valine .vwrap .vcontrol .col.col-60{text-align:left}.valine .vlist .vcard .vhead,.valine .vlist .vcard section .vfooter{text-align:left}.valine .vlist .vcard section{padding-bottom:.5rem!important}.vname{color:#42b983!important}.valine .vinfo .col{text-align:left;margin-left:-27rem}div#comment-div{margin-bottom:-8rem}.valine .vlist .vcard .vcontent .code,.valine .vlist .vcard .vcontent code,.valine .vlist .vcard .vcontent pre{background:#fbfbfb}.valine .vlist .vcard .vcontent a{color:#42b983}.valine .vlist .vcard .vimg{border-radius:3px}.valine .vbtn{border-radius:2px;padding:.3rem 1.25rem}.valine .vbtn:active,.valine .vbtn:hover{color:#42b983;border-color:#42b983;background-color:#fff}.valine .vwrap .vheader .vinput:focus{border-bottom-color:#42b983}.valine .vlist .vcard .vcontent.expand:before{background:-webkit-gradient(linear,left top,left bottom,from(hsla(0,0%,100%,0)),to(hsla(0,0%,100%,.2)));background:linear-gradient(180deg,hsla(0,0%,100%,0),hsla(0,0%,100%,.2))}.valine .vlist .vcard .vcontent.expand:after{content:"点击展开";font-size:.4rem;text-align:right;left:-1rem;background:hsla(0,0%,100%,.2)}.valine .vlist .vcard section .vfooter .vat{color:#b3b3b3}.valine .vlist .vcard section .vfooter .vat:hover{color:#42b983}.vcontent img{margin:0}.valine .info{display:none}</style><script type="text/javascript" src="/js/av.min.js"></script><script type="text/javascript" src="/js/Valine.min.js"></script><script type="text/javascript" src="/js/activate-power-mode.js"></script><script>POWERMODE.colorful=!0,POWERMODE.shake=!1,document.body.addEventListener("input",POWERMODE),new Valine({el:"#comment-div",notify:!1,verify:!0,appId:"SMcDFP1bN1jgb9Lo8JmtICHm-gzGzoHsz",appKey:"dH4nrUrt3V5XiJiI6Qyejnbi",placeholder:"",path:window.location.pathname,avatar:"monsterid",guest_info:["nick","mail","link"]});var chicken='<a href="#"><img src="https://image.uisdc.com/wp-content/uploads/2018/06/uisdc-chat-chicken.gif" class="checken"></a>';$("#comment-div").prepend(chicken)</script></div></div><script>var $bqinline=$("img[alt='bq-inline']");$bqinline.css({width:"2.3rem",height:"2.3rem",display:"inline","vertical-align":"text-bottom"})</script></div><div class="comments" id="comments"></div></div><aside id="sidebar" class="sidebar" onselectstart="return!1"><div class="sidebar-inner"><ul class="sidebar-nav motion-element"><li class="sidebar-nav-toc sidebar-nav-active" data-target="post-toc-wrap">Contents</li><li class="sidebar-nav-overview" data-target="site-overview">Site Preview</li></ul><section class="site-overview sidebar-panel"><div class="sidebar-sticky sticky"><div itemscope itemtype="http://schema.org/Person"><div class="author__avatar"><img src="/images/blogImage.jpg" class="author__avatar" alt="MrBird" itemprop="image"></div><div class="author__content"><h3 class="author__name" itemprop="name">MrBird's Blog</h3><p class="author__bio" itemprop="description">A simple blog, code repository, just keep blogging</p></div><div class="author__urls-wrapper"><button class="btn btn--inverse">Follow</button><ul class="author__urls social-icons"><li><a href="http://map.baidu.com/?newmap=1&s=s%26wd%3D%E7%A6%8F%E5%B7%9E%E5%B8%82%26c%3D300&from=alamap&tpl=mapcity" target="_blank" itemprop="url" class="ignore-href"><i class="fa fa-fw fa-map-marker" aria-hidden="true"></i>&nbsp;&nbsp;FuZhou,CN</a></li><li><a href="https://love.mrbird.cc" target="_blank" itemprop="url" class="ignore-href"><i class="fa fa-fw fa-diamond" aria-hidden="true"></i>&nbsp;&nbsp;Love</a></li><li><a href="https://cloud.mrbird.cn" target="_blank" itemprop="url" class="ignore-href"><i class="fa fa-fw fa-chain" aria-hidden="true"></i>&nbsp;&nbsp;FEBS</a></li><li><a href="/atom.xml" target="_blank" itemprop="url" class="ignore-href"><i class="fa fa-fw fa-rss" aria-hidden="true"></i>&nbsp;&nbsp;RSS</a></li><li><a href="https://gitee.com/mrbirdd" target="_blank" itemprop="sameAs" class="ignore-href"><i class="fa fa-fw fa-codepen" aria-hidden="true"></i>&nbsp;&nbsp;Gitee</a></li><li><a href="https://github.com/wuyouzhuguli" target="_blank" itemprop="sameAs" class="ignore-href"><i class="fa fa-fw fa-github-alt" aria-hidden="true"></i>&nbsp;&nbsp;GitHub</a></li><li><a href="javascript:;" class="popup-trigger"><i class="fa fa-fw fa-search" aria-hidden="true"></i>&nbsp;&nbsp;Search</a></li></ul></div></div></div><script>var $urls=$(".author__urls").find("a");$urls.each(function(){var o=$(this);o.mouseenter(function(){o.css({color:"#414547"})}),o.mouseleave(function(){o.css({color:""})})})</script></section><section class="post-toc-wrap motion-element sidebar-panel sidebar-panel-active"><div class="post-toc"><div class="post-toc-content"><ol class="nav"><li class="nav-item nav-level-2"><a class="nav-link" href="#字符函数"><span class="nav-number">1.</span> <span class="nav-text">字符函数</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#字符串截取"><span class="nav-number">1.1.</span> <span class="nav-text">字符串截取</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#查找子串位置"><span class="nav-number">1.2.</span> <span class="nav-text">查找子串位置</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#字符串连接"><span class="nav-number">1.3.</span> <span class="nav-text">字符串连接</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#去除空格"><span class="nav-number">1.4.</span> <span class="nav-text">去除空格</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#去除前缀和后缀"><span class="nav-number">1.5.</span> <span class="nav-text">去除前缀和后缀</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#计算字符串长度"><span class="nav-number">1.6.</span> <span class="nav-text">计算字符串长度</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#initcap（首字母变大写）-lower（变小写）-upper（变大写）"><span class="nav-number">1.7.</span> <span class="nav-text">initcap（首字母变大写） ,lower（变小写）,upper（变大写）</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#替换"><span class="nav-number">1.8.</span> <span class="nav-text">替换</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#decode-实现if-then-逻辑-注-第一个是表达式-最后一个是不满足任何一个条件的值"><span class="nav-number">1.9.</span> <span class="nav-text">decode[实现if ..then 逻辑] 注:第一个是表达式,最后一个是不满足任何一个条件的值</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#case-实现switch-case-逻辑"><span class="nav-number">1.10.</span> <span class="nav-text">case[实现switch ..case 逻辑]</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#十进制和ASCII互相转换"><span class="nav-number">1.11.</span> <span class="nav-text">十进制和ASCII互相转换</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#日期函数"><span class="nav-number">2.</span> <span class="nav-text">日期函数</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#日期字符串相互转换"><span class="nav-number">2.1.</span> <span class="nav-text">日期字符串相互转换</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#next-day"><span class="nav-number">2.2.</span> <span class="nav-text">next_day</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#两个日期相差天数"><span class="nav-number">2.3.</span> <span class="nav-text">两个日期相差天数</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#months-between"><span class="nav-number">2.4.</span> <span class="nav-text">months_between</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#round-舍入到最接近的日期-（day-舍入到最接近的星期日）"><span class="nav-number">2.5.</span> <span class="nav-text">round [舍入到最接近的日期]（day:舍入到最接近的星期日）</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#计算时间差"><span class="nav-number">2.6.</span> <span class="nav-text">计算时间差</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#查找月的最后一天"><span class="nav-number">2.7.</span> <span class="nav-text">查找月的最后一天</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#ADD-MONTHS-date，i"><span class="nav-number">2.8.</span> <span class="nav-text">ADD_MONTHS(date，i)</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#EXTRACT"><span class="nav-number">2.9.</span> <span class="nav-text">EXTRACT</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#数字函数"><span class="nav-number">3.</span> <span class="nav-text">数字函数</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#取整函数（ceil-向上取整-floor-向下取整）"><span class="nav-number">3.1.</span> <span class="nav-text">取整函数（ceil 向上取整,floor 向下取整）</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#取幂-power-和-求平方根-sqrt"><span class="nav-number">3.2.</span> <span class="nav-text">取幂(power) 和 求平方根(sqrt)</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#求余"><span class="nav-number">3.3.</span> <span class="nav-text">求余</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#返回固定小数位数-（round-四舍五入，trunc-直接截断）"><span class="nav-number">3.4.</span> <span class="nav-text">返回固定小数位数 （round:四舍五入，trunc:直接截断）</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#返回值的符号（正数返回为1-负数为-1）"><span class="nav-number">3.5.</span> <span class="nav-text">返回值的符号（正数返回为1,负数为-1）</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#LEAST、GREATEST"><span class="nav-number">3.6.</span> <span class="nav-text">LEAST、GREATEST</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#其他函数"><span class="nav-number">4.</span> <span class="nav-text">其他函数</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#vsize-返回表达式所需的字节数"><span class="nav-number">4.1.</span> <span class="nav-text">vsize: 返回表达式所需的字节数</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#nvl-ex1-ex2"><span class="nav-number">4.2.</span> <span class="nav-text">nvl(ex1,ex2):</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#nullif-ex1-ex2"><span class="nav-number">4.3.</span> <span class="nav-text">nullif(ex1,ex2):</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#nvl2-ex1-ex2-ex3"><span class="nav-number">4.4.</span> <span class="nav-text">nvl2(ex1,ex2,ex3) :</span></a></li></ol></li></ol></div></div></section></div></aside></div></main><footer id="footer" class="footer" onselectstart="return!1"><div class="footer-inner"><div class="copyright">&copy; 2016 - <span itemprop="copyrightYear">2019</span>&nbsp;&nbsp; <span class="author" itemprop="copyrightHolder">MrBird</span>&nbsp;&nbsp;|<script async src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>&nbsp;&nbsp;UV&nbsp;<span class="busuanzi-value" id="busuanzi_value_site_uv" style="cursor:pointer" title="统计开始时间：2019年7月5日"></span> &nbsp;&nbsp;PV&nbsp;<span class="busuanzi-value" id="busuanzi_value_site_pv" style="cursor:pointer" title="统计开始时间：2019年7月5日"></span></div></div></footer><div class="back-to-top"><span style="font-family:'Source Sans Pro','Helvetica Neue',Arial,sans-serif;font-size:1.2em;font-weight:600">TOP</span></div></div><script type="text/javascript">"[object Function]"!==Object.prototype.toString.call(window.Promise)&&(window.Promise=null)</script><script type="text/javascript" src="/lib/jquery/index.js?v=2.1.3"></script><script type="text/javascript" src="/lib/fastclick/lib/fastclick.min.js?v=1.0.6"></script><script type="text/javascript" src="/lib/jquery_lazyload/jquery.lazyload.js?v=1.9.7"></script><script type="text/javascript" src="/lib/velocity/velocity.min.js?v=1.2.1"></script><script type="text/javascript" src="/lib/velocity/velocity.ui.min.js?v=1.2.1"></script><script type="text/javascript" src="/js/src/utils.js?v=5.1.1"></script><script type="text/javascript" src="/js/src/motion.js?v=5.1.1"></script><script type="text/javascript" src="/js/src/scrollspy.js?v=5.1.1"></script><script type="text/javascript" src="/js/src/post-details.js?v=5.1.1"></script><script type="text/javascript" src="/js/src/bootstrap.js?v=5.1.1"></script><script type="text/javascript">function proceedsearch(){$("body").append('<div class="search-popup-overlay local-search-pop-overlay"></div>').css("overflow","hidden"),$(".search-popup-overlay").click(onPopupClose),$(".popup").toggle();var t=$("#local-search-input");t.attr("autocapitalize","none"),t.attr("autocorrect","off"),t.focus()}var isfetched=!1,isXml=!0,search_path="search.xml";0===search_path.length?search_path="search.xml":search_path.endsWith("json")&&(isXml=!1);var path="/"+search_path,onPopupClose=function(t){$(".popup").hide(),$("#local-search-input").val(""),$(".search-result-list").remove(),$("#no-result").remove(),$(".local-search-pop-overlay").remove(),$("body").css("overflow","")},searchFunc=function(t,e,o){"use strict";$("body").append('<div class="search-popup-overlay local-search-pop-overlay"><div id="search-loading-icon"><i class="fa fa-spinner fa-pulse fa-2x fa-fw"></i></div></div>').css("overflow","hidden"),$("#search-loading-icon").css("margin","20% auto 0 auto").css("text-align","center"),$.ajax({url:t,dataType:isXml?"xml":"json",async:!0,success:function(t){isfetched=!0,$(".popup").detach().appendTo(".header-inner");var n=isXml?$("entry",t).map(function(){return{title:$("title",this).text(),content:$("content",this).text(),url:$("url",this).text()}}).get():t,r=document.getElementById(e),s=document.getElementById(o),a=function(){var t=r.value.trim().toLowerCase(),e=t.split(/[\s\-]+/);e.length>1&&e.push(t);var o=[];if(t.length>0&&n.forEach(function(n){function r(e,o,n,r){for(var s=r[r.length-1],a=s.position,i=s.word,l=[],h=0;a+i.length<=n&&0!=r.length;){i===t&&h++,l.push({position:a,length:i.length});var p=a+i.length;for(r.pop();0!=r.length&&(s=r[r.length-1],a=s.position,i=s.word,p>a);)r.pop()}return c+=h,{hits:l,start:o,end:n,searchTextCount:h}}function s(t,e){var o="",n=e.start;return e.hits.forEach(function(e){o+=t.substring(n,e.position);var r=e.position+e.length;o+='<b class="search-keyword">'+t.substring(e.position,r)+"</b>",n=r}),o+=t.substring(n,e.end)}var a=!1,i=0,c=0,l=n.title.trim(),h=l.toLowerCase(),p=n.content.trim().replace(/<[^>]+>/g,""),u=p.toLowerCase(),f=decodeURIComponent(n.url),d=[],g=[];if(""!=l&&(e.forEach(function(t){function e(t,e,o){var n=t.length;if(0===n)return[];var r=0,s=[],a=[];for(o||(e=e.toLowerCase(),t=t.toLowerCase());(s=e.indexOf(t,r))>-1;)a.push({position:s,word:t}),r=s+n;return a}d=d.concat(e(t,h,!1)),g=g.concat(e(t,u,!1))}),(d.length>0||g.length>0)&&(a=!0,i=d.length+g.length)),a){[d,g].forEach(function(t){t.sort(function(t,e){return e.position!==t.position?e.position-t.position:t.word.length-e.word.length})});var v=[];0!=d.length&&v.push(r(l,0,l.length,d));for(var C=[];0!=g.length;){var $=g[g.length-1],m=$.position,x=$.word,w=m-20,y=m+80;w<0&&(w=0),y<m+x.length&&(y=m+x.length),y>p.length&&(y=p.length),C.push(r(p,w,y,g))}C.sort(function(t,e){return t.searchTextCount!==e.searchTextCount?e.searchTextCount-t.searchTextCount:t.hits.length!==e.hits.length?e.hits.length-t.hits.length:t.start-e.start});var T=parseInt("1");T>=0&&(C=C.slice(0,T));var b="";b+=0!=v.length?"<li><a href='"+f+"' class='search-result-title'>"+s(l,v[0])+"</a>":"<li><a href='"+f+"' class='search-result-title'>"+l+"</a>",C.forEach(function(t){b+="<a href='"+f+'\'><p class="search-result">'+s(p,t)+"...</p></a>"}),b+="</li>",o.push({item:b,searchTextCount:c,hitCount:i,id:o.length})}}),1===e.length&&""===e[0])s.innerHTML='<div id="no-result"><i class="fa fa-search fa-5x" /></div>';else if(0===o.length)s.innerHTML='<div id="no-result"><i class="fa fa-frown-o fa-5x" /></div>';else{o.sort(function(t,e){return t.searchTextCount!==e.searchTextCount?e.searchTextCount-t.searchTextCount:t.hitCount!==e.hitCount?e.hitCount-t.hitCount:e.id-t.id});var a='<ul class="search-result-list">';o.forEach(function(t){a+=t.item}),a+="</ul>",s.innerHTML=a}};r.addEventListener("input",a),$(".local-search-pop-overlay").remove(),$("body").css("overflow",""),proceedsearch()}})};$(".popup-trigger").click(function(t){t.stopPropagation(),isfetched===!1?searchFunc(path,"local-search-input","local-search-result"):proceedsearch()}),$(".popup-btn-close").click(onPopupClose),$(".popup").click(function(t){t.stopPropagation()}),$(document).on("keyup",function(t){var e=27===t.which&&$(".search-popup").is(":visible");e&&onPopupClose()})</script></body><script>$(function(){$("a").not(".nav-link,.cloud-tie-join-count,.ignore-href,.jstree-anchor").addClass("animsition-link")});var burst1=new mojs.Burst({left:0,top:0,radius:{5:40},children:{shape:"circle",fill:["red","cyan","orange"],fillOpacity:.8,radiusX:3.5,radiusY:3.5}});document.addEventListener("click",function(a){null==a.target.href&&"footer"!=a.target.className&&"copyright"!=a.target.className&&"author__urls social-icons"!=a.target.className&&"author__avatar"!=a.target.className&&"sidebar sidebar-active"!=a.target.className&&burst1.tune({x:a.pageX,y:a.pageY}).generate().replay()})</script><script type="text/javascript" src="/js/message.js"></script></html><!-- rebuild by neat -->